﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Potato.Model;
using OMG.Fill.Potato.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
namespace OMG.Fill.Potato.Services
{
    /// <summary>
    /// 马铃薯种薯生产情况表通用操作类
    /// </summary>
    public class PotSeedServices: PotSeeds
    {
        public Pager<List<PotSeedEntity>> GetPageList(PotSeedQuery query)
        {
            Pager<List<PotSeedEntity>> obj = new Pager<List<PotSeedEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_PotSeed where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<PotSeedEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取马铃薯种薯生产情况表分页列表失败", ex);
                return obj;
            }
        }

        public ResResult<MyReportModel> GetModel(string AreaCode, string FillInYear)
        {
            ReportServices reportService = new ReportServices();
            ResResult<MyReportModel> obj = new ResResult<MyReportModel>();
            List<PotSeedEntity> list = new List<PotSeedEntity>();
            MyReportModel model = new MyReportModel();
            int oldFillInYear = Convert.ToInt32(FillInYear) - 1;
            string strSql = "select * from T_PotSeed where ReportId = @ReportId";
            if (reportService.CheckRepCreate("PotSeed", AreaCode, FillInYear))
            {
                obj.status = DefaultKey.ErrorResult;
                obj.model = model;
                obj.message = FillInYear + "年的全年生产情况表没有生成！";
            }
            else
            {
                ReportEntity reportModel = reportService.GetReportView("PotSeed", AreaCode, FillInYear);
                model.ReportStatus = reportModel.Status;
                model.ReportId = reportModel.Id;
                model.Opinion = reportModel.Opinion;
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    if (reportService.CheckRepCount(model.ReportId, "T_PotSeed"))
                    {
                        PotSeedEntity entity = new PotSeedEntity();
                        entity.ReportId = model.ReportId;
                        list.Add(entity);
                    }
                    else
                    {
                        list = dbCon.Query<PotSeedEntity>(strSql, new { ReportId = model.ReportId }).ToList();
                    }
                    model.list = list;
                }
                obj.model = model;
                obj.status = DefaultKey.SuccessResult;
                obj.message = "数据获取成功！";
            }
            return obj;
        }


        public List<PotSeedEntity> ExcelToModel(string pathName, string fileSuffix, string ReportId)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            List<PotSeedEntity> list = new List<PotSeedEntity>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            sheet = workbook.GetSheetAt(0);
            if (sheet != null)
            {
                int startRow =3;
                IRow firstRow = sheet.GetRow(startRow);//从第4行开始读
                int cellCount = firstRow.LastCellNum;//获取列数

                //获取所有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    PotSeedEntity model = new PotSeedEntity();
                    if (row.GetCell(0) != null)
                    {
                        if (row.GetCell(0).CellType == CellType.Formula)
                        {
                            model.ZPyield = Decimal.Round(Convert.ToDecimal(row.GetCell(0).NumericCellValue), 4);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(0).ToString()))
                            {
                                model.ZPyield = Convert.ToDecimal(row.GetCell(0).ToString());
                            }
                            else
                            {
                                model.ZPyield = null;
                            }
                        }
                    }
                    if(row.GetCell(1) != null)
                    {
                        model.ZPvariety = row.GetCell(1).ToString();
                    }                    
                    //////////////////////
                    if (row.GetCell(2) != null)
                    {
                        if (row.GetCell(2).CellType == CellType.Formula)
                        {
                            model.YYZdpArea = Decimal.Round(Convert.ToDecimal(row.GetCell(2).NumericCellValue), 2);//全年合计总产(万吨）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(2).ToString()))
                            {
                                model.YYZdpArea = Convert.ToDecimal(row.GetCell(2).ToString());
                            }
                            else
                            {
                                model.YYZdpArea = null;
                            }
                        }
                    }

                    if (row.GetCell(3) != null)
                    {
                        if (row.GetCell(3).CellType == CellType.Formula)
                        {
                            model.YYZsingleArea = Decimal.Round(Convert.ToDecimal(row.GetCell(3).NumericCellValue), 2);//全年合计总产值(万元）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(3).ToString()))
                            {
                                model.YYZsingleArea = Convert.ToDecimal(row.GetCell(3).ToString());
                            }
                            else
                            {
                                model.YYZsingleArea = null;
                            }
                        }
                    }

                    if (row.GetCell(4) != null)
                    {
                        if (row.GetCell(4).CellType == CellType.Formula)
                        {
                            model.YYZavgYield = Decimal.Round(Convert.ToDecimal(row.GetCell(4).NumericCellValue), 2);//大春作面积(万亩）
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(4).ToString()))
                            {
                                model.YYZavgYield = Convert.ToDecimal(row.GetCell(4).ToString());
                            }
                            else
                            {
                                model.YYZavgYield = null;
                            }
                        }
                    }
                    if(row.GetCell(5) != null)
                    {
                        model.YYZvariety = row.GetCell(5).ToString();
                    }
                    if (row.GetCell(6) != null)
                    {
                        if (DataCheck.isNumber(row.GetCell(6).ToString()))
                        {
                            model.YYZplantTimes = Convert.ToInt32(row.GetCell(6).ToString());
                        }
                        else
                        {
                            model.YYZplantTimes = null;
                        }                        
                    }
                    //////////////////////////////////////
                    if (row.GetCell(7) != null)
                    {
                        if (row.GetCell(7).CellType == CellType.Formula)
                        {
                            model.YZarea = Decimal.Round(Convert.ToDecimal(row.GetCell(7).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(7).ToString()))
                            {
                                model.YZarea = Convert.ToDecimal(row.GetCell(7).ToString());
                            }
                            else
                            {
                                model.YZarea = null;
                            }
                        }
                    }

                    if (row.GetCell(8) != null)
                    {
                        if (row.GetCell(8).CellType == CellType.Formula)
                        {
                            model.YZmuYield = Decimal.Round(Convert.ToDecimal(row.GetCell(8).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(8).ToString()))
                            {
                                model.YZmuYield = Convert.ToDecimal(row.GetCell(8).ToString());
                            }
                            else
                            {
                                model.YZmuYield = null;
                            }
                        }
                    }
                    if(row.GetCell(9) != null)
                    model.YZyyzOrigin = row.GetCell(9).ToString();
                    if(row.GetCell(10) != null)
                    model.YZvariety = row.GetCell(10).ToString();
                    if(row.GetCell(11) != null)
                    model.YZoriginPlace = row.GetCell(11).ToString();
                    //////////////////////////////////////////////////
                    if (row.GetCell(12) != null)
                    {
                        if (row.GetCell(12).CellType == CellType.Formula)
                        {
                            model.YJZarea = Decimal.Round(Convert.ToDecimal(row.GetCell(12).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(12).ToString()))
                            {
                                model.YJZarea = Convert.ToDecimal(row.GetCell(12).ToString());
                            }
                            else
                            {
                                model.YJZarea = null;
                            }
                        }
                    }

                    if (row.GetCell(13) != null)
                    {
                        if (row.GetCell(13).CellType == CellType.Formula)
                        {
                            model.YJZmuYield = Decimal.Round(Convert.ToDecimal(row.GetCell(13).NumericCellValue), 2);
                        }
                        else
                        {
                            if (DataCheck.isNumber(row.GetCell(13).ToString()))
                            {
                                model.YJZmuYield = Convert.ToDecimal(row.GetCell(13).ToString());
                            }
                            else
                            {
                                model.YJZmuYield = null;
                            }
                        }
                    }
                    if(row.GetCell(14) != null)
                        model.YJZvariety = row.GetCell(14).ToString();
                    if (row.GetCell(15) != null)
                        model.YJZoriginPlace = row.GetCell(15).ToString();
                    if (row.GetCell(16) != null)
                        model.Remark = row.GetCell(16).ToString();

                    model.ReportId = ReportId;
                    list.Add(model);

                }

            }

            return list;
        }
        public ResResult<List<SeedStatisModel>> getStatisListData(string AreaCode, string FillInYear)
        {
            ResResult<List<SeedStatisModel>> obj = new ResResult<List<SeedStatisModel>>();
            List<SeedStatisModel> list = new List<SeedStatisModel>();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                list = getProvinStatisList(FillInYear);
                //var citylist = areaServices.GetPrefectureList();
                //SeedStatisModel totalModel = new SeedStatisModel();
                //totalModel.AreaCode = AreaCode;
                //totalModel.AreaName = "合计";
                //totalModel.tag = 2;
                //totalModel.ZPyield = 0;
                //totalModel.YYZdpArea = 0;
                //totalModel.YZarea = 0;
                //totalModel.YZYield = 0;
                //totalModel.YJZarea = 0;
                //totalModel.YJZYield = 0;
                //foreach (string code in citylist)
                //{
                //    List<SeedStatisModel> templist = getAreaStatisList(code,FillInYear);
                    
                //    int len = templist.Count;
                //    if (len != 0)
                //    {
                //        SeedStatisModel model = templist[len - 1];
                        
                //        model.AreaName = areaServices.GetByCodeModel(code).model.Name;
                //        model.tag = 1;
                //        totalModel.ZPyield += model.ZPyield;
                //        totalModel.YYZdpArea += model.YYZdpArea;
                //        totalModel.YZarea += model.YZarea;
                //        totalModel.YZYield += model.YZYield;
                //        totalModel.YJZarea += model.YJZarea;
                //        totalModel.YJZYield += model.YJZYield;
                //        list.Add(model);
                //    }
                //}
                //list.Add(totalModel);
            }
            else if (AreaCode.Substring(4, 8).Equals("00000000"))
            {
                list = getAreaStatisList(AreaCode, FillInYear);
                if (list !=null && list.Count != 0)
                {
                    int len = list.Count;
                    list[len - 1].tag = 1;
                }

            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }

        public List<SeedStatisModel> getProvinStatisList(string FillInYear)
        {
            List<SeedStatisModel> list = new List<SeedStatisModel>();
            string strSql = "select ta.FullName AreaName ,tt.* from ( ";
            strSql += " select case when grouping(t1.ParentCode)=1 then '530000000000' else ParentCode end AreaCode , ";
            strSql += " isnull(sum(t3.ZPyield),0.00) ZPyield, ";
            strSql += " isnull(sum(t3.YYZdpArea),0.00) YYZdpArea,";
            strSql += " isnull(sum(t3.YZarea),0.00) YZarea, ";
            strSql += " isnull(cast(sum(t3.YZarea*t3.YZmuYield/1000)as decimal(10,2)),0.00)YZYield, ";
            strSql += " isnull(sum(t3.YJZarea),0.00) YJZarea,  ";
            strSql += " isnull(cast(sum(t3.YJZarea*t3.YJZmuYield/1000)as decimal(10,2)),0.00)YJZYield from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotSeed t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotSeed'  and t2.FillInYear = @FillInYear  ";
            strSql += " and (t1.ParentCode != '530000000000' or t1.Code != '530000000000') ";
            strSql += " and t2.status=  '4' ";
            strSql += " group by rollup(t1.ParentCode)";
            strSql += " ) tt left join T_Area ta on tt.AreaCode = ta.Code";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<SeedStatisModel>(strSql, new { FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放

                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }
        public List<SeedStatisModel> getAreaStatisList(string AreaCode, string FillInYear)
        {
            List<SeedStatisModel> list = new List<SeedStatisModel>();
            string strSql = "select ta.FullName AreaName ,tt.* from ( ";
            strSql += "select case when grouping(t1.Code)=1 then '"+ AreaCode+"' else Code end AreaCode, ";
            strSql += " isnull(sum(t3.ZPyield),0.00) ZPyield, ";
            strSql += " isnull(sum(t3.YYZdpArea),0.00) YYZdpArea,";
            strSql += " isnull(sum(t3.YZarea),0.00) YZarea, ";
            strSql += " isnull(cast(sum(t3.YZarea*t3.YZmuYield/1000)as decimal(10,2)),0.00)YZYield, ";
            strSql += " isnull(sum(t3.YJZarea),0.00) YJZarea,  ";
            strSql += " isnull(cast(sum(t3.YJZarea*t3.YJZmuYield/1000)as decimal(10,2)),0.00)YJZYield from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotSeed t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotSeed' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear  ";
            strSql += " and t2.status=  '4' ";
            strSql += " group by rollup(t1.Code)";
            strSql += " ) tt left join T_Area ta on tt.AreaCode = ta.Code";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<SeedStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                   
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }


    }
}